package com.bw.mapper;

import com.bw.domain.Sugar;
import com.bw.domain.SugarThree;
import com.bw.domain.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT p.`name`,sum(i.final_total_amount) value FROM order_info i,base_province p WHERE i.province_id=p.id GROUP BY p.`name`")
    List<Sugar> getSugarList1();

    @Select("SELECT t.`name`,sum(final_total_amount) value FROM order_info i,order_detail d,sku_info k,base_trademark t WHERE i.id=d.order_id and d.sku_id=k.id and k.tm_id=t.id GROUP BY t.`name`")
    List<Sugar> getSugarList2();

    @Select("SELECT c1.name `name`,sum(final_total_amount) value FROM order_info i,order_detail d,sku_info k,base_category3 c3,base_category2 c2,base_category1 c1 WHERE i.id=d.order_id and d.sku_id=k.id and k.category3_id=c3.id and c3.category2_id=c2.id and c2.category1_id=c1.id GROUP BY c1.name")
    List<Sugar> getSugarList3();

    @Select("\n" +
            "SELECT * FROM\n" +
            "(SELECT count(*) news from user_info WHERE TIMESTAMPDIFF(day,create_time,NOW())<14) a,\n" +
            "(SELECT count(*) olds from user_info WHERE TIMESTAMPDIFF(day,create_time,NOW())>=14) b")
    List<SugarTwo> getSugarList4();

    @Select("SELECT c2.name `name`,sum(final_total_amount) value FROM order_info i,order_detail d,sku_info k,base_category3 c3,base_category2 c2 WHERE i.id=d.order_id and d.sku_id=k.id and k.category3_id=c3.id and c3.category2_id=c2.id GROUP BY c2.name\n")
    List<Sugar> getSugarList5();

    @Select("SELECT CONCAT(SUBSTRING(birthday,3,1),'0后') name,sum(final_total_amount) value FROM user_info u,order_info o WHERE u.id=o.user_id GROUP BY CONCAT(SUBSTRING(birthday,3,1),'0后')")
    List<Sugar> getSugarList6();

    @Select("SELECT yys name,sum(final_total_amount) value from order_info WHERE yys is not null GROUP BY yys")
    List<Sugar> getSugarList7();

    @Select("SELECT t.name,IF(u.gender='F','男','女') namee,sum(final_total_amount) value from user_info u,order_info i,order_detail d,sku_info k,base_trademark t where u.id=i.user_id AND  i.id=d.order_id and d.sku_id=k.id and k.tm_id=t.id GROUP BY t.name,IF(u.gender='F','男','女')")
    List<SugarThree> getSugarList8();

    @Select("CALL sp()")
    Double getSugarList9();

    @Select("\n" +
            "SELECT p.spu_name name,sum(final_total_amount) value FROM order_info i,order_detail d,sku_info k,spu_info p WHERE i.id=d.order_id and d.sku_id=k.id AND k.spu_id=p.id GROUP BY p.spu_name")
    List<Sugar> getSugarLis10();
}
